library(dplyr)
library(data.table)
library(writexl)


# For diagnosis data:
diagnosis_vars <- c("age_calc", "agecatf", "religion", "eduf", "diabetesknow",
                    "heartknow", "strokeknow", "female", "runningvar",
                    "runningvar_female", "runningvar_male", "sys", "dias")
# Use the .. prefix because data_diagnosis is a data.table
tables.data <- data_diagnosis[,..diagnosis_vars]

# Function to calculate range within a given bandwidth
get_range <- function(data,dataset_label,group_label) {
  tdata<-data
  # Calculate range for sys and dias
  sys_rng <- range(tdata$sys, na.rm = TRUE)
  dias_rng <- range(tdata$dias, na.rm = TRUE)
  
  # Calculate median for sys and dias
  sys_med <- median(tdata$sys, na.rm = TRUE)
  dias_med <- median(tdata$dias, na.rm = TRUE)
  
  tibble(
    dataset   = dataset_label,
    group     = group_label,
    sys_min   = sys_rng[1],
    sys_med   = sys_med,
    sys_max   = sys_rng[2],
    dias_min  = dias_rng[1],
    dias_med  = dias_med,
    dias_max  = dias_rng[2]
  )
}
# Save diagnosis results
#full sample

diag_total_overall  <- get_range(tables.data,"Overall", "Total")

diag_female_overall <- tables.data %>%
  filter(female == 1)
diag_female_overall <- get_range(diag_female_overall ,"Overall", "Female")

diag_male_overall <- tables.data %>%
  filter(female == 0)
diag_male_overall <- get_range(diag_male_overall ,"Overall", "Male")

#within bandwidth
tdata_overall <- tables.data  %>% filter(runningvar >= -bwTdiag, runningvar <= bwTdiag)

diag_total  <- get_range(tdata_overall,"Within BW", "Total")
diag_female <- tables.data %>%
  filter(female == 1)%>% filter(runningvar_female >= -bwFdiag, runningvar_female <= bwFdiag) 

diag_female <- get_range(diag_female ,"Within BW", "Female")
  
diag_male <- tables.data %>%
  filter(female == 0)%>% filter(runningvar_male >= -bwMdiag, runningvar_male <= bwMdiag)  
diag_male <-  get_range(diag_male,"Within BW", "Male")

diagnosis_range_table <- bind_rows(diag_total_overall,diag_female_overall,diag_male_overall,diag_total,diag_female,diag_male)

# For treatment data:

tables.data <- data_treatment[,..diagnosis_vars]

# Save treatment results
#full sample

treat_total_overall  <- get_range(tables.data,"Overall", "Total")

treat_female_overall <- tables.data %>%
  filter(female == 1)
treat_female_overall <- get_range(treat_female_overall ,"Overall", "Female")

treat_male_overall <- tables.data %>%
  filter(female == 0)
treat_male_overall <- get_range(treat_male_overall ,"Overall", "Male")

#within bandwidth
tdata_overall <- tables.data  %>% filter(runningvar >= -bwTtreat, runningvar <= bwTtreat)

treat_total  <- get_range(tdata_overall,"Within BW", "Total")
treat_female <- tables.data %>%
  filter(female == 1)%>% filter(runningvar_female >= -bwFtreat, runningvar_female <= bwFtreat) 

treat_female <- get_range(treat_female ,"Within BW", "Female")

treat_male <- tables.data %>%
  filter(female == 0)%>% filter(runningvar_male >= -bwMtreat, runningvar_male <= bwMtreat)  
treat_male <-  get_range(treat_male,"Within BW", "Male")

treatment_range_table <- bind_rows(treat_total_overall,treat_female_overall,treat_male_overall,treat_total,treat_female,treat_male)


# Combine diagnosis and treatment results into one table

# Print the final table
print(treatment_range_table)



# Create a list with each element corresponding to a sheet
final_output <- list(
  "Diagnosis" = diagnosis_range_table,
  "Treatment" = treatment_range_table
)

# Write the Excel file with two sheets: one for Diagnosis and one for Treatment
write_xlsx(final_output, path = "final_range_table.xlsx")

